package com.app.TCES.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.app.TCES.domain.Student;
import com.app.TCES.mapper.UploadMapper;

@Service
public class UploadService {

	Logger log = LogManager.getLogger(UploadService.class);
	@Autowired
	UploadMapper uploadMapper;

	@SuppressWarnings("resource")
	public String readExcel(MultipartFile file) throws Exception {

		Workbook wb;

		InputStream is;
		String rowMessage = "";
		List<Student> list = new ArrayList<Student>();
		String fileName = file.getOriginalFilename();

		boolean isExcel2003 = true;
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}

		is = file.getInputStream();

		if (isExcel2003) {
			wb = new HSSFWorkbook(is);
		} else {
			wb = new XSSFWorkbook(is);
		}
		try {

			Sheet sheet = wb.getSheetAt(0);
			// 得到总行数
			int totalRows = sheet.getPhysicalNumberOfRows();// getPhysicalNumberOfRows()是获取不为空的行个数
			// 总列数
			int totalCells = 0;
			// 得到Excel的列数(前提是有行数),从第二行算起
			if (totalRows >= 2 && sheet.getRow(1) != null) {
				totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			} else {
				rowMessage = "数据表格没有数据";
				return rowMessage;
			}

			log.debug("总行数:" + totalRows + "总列数:" + totalCells);
			if (totalCells != 4) {
				rowMessage ="文件模板错误，请查看模板格式";
				return rowMessage;
			}
			
			for (int i = 0; i < 1; i++) {
				Row row = sheet.getRow(0);
				// 循环第一行,是否与模板一样
				
					for (int j = 0; j < totalCells; j++) {
						Cell cell = row.getCell(j);
						if (cell != null) {
							if (j == 0) {
								if (cell.getStringCellValue().equals("学号")) {
									continue;
								} else {
									rowMessage = "文件模板错误，请查看模板格式";
								}
							} else if (j == 1) {
								if (cell.getStringCellValue().equals("姓名")) {
									continue;
								} else {
									rowMessage = "文件模板错误，请查看模板格式";
								}
							} else if (j == 2) {
								if (cell.getStringCellValue().equals("学院名称")) {
									continue;
								} else {
									rowMessage = "文件模板错误，请查看模板格式";
								}
							} else {
								if (cell.getStringCellValue().equals("班级名称")) {
									continue;
								} else {
									rowMessage = "文件模板错误，请查看模板格式";
								}
							}
						}
					}
				}
			
			
			
			// 循环Excel行数,从第二行开始。标题不入库
			for (int r = 1; r < totalRows; r++) {
				Row row = sheet.getRow(r);
				if (row == null || 0 == row.getPhysicalNumberOfCells()) {
					rowMessage = "数据表格没有数据";
					return rowMessage;
				}

				Student s = new Student();

				int stuId = 0; // 学生学号
				int deptId;
				int classId;
				String stuName = ""; // 学生姓名
				String deptName = ""; // 院系名称
				String className = ""; // 班级名称

				 

					// 循环Excel的列
					for (int c = 0; c < totalCells; c++) {
						Cell cell = row.getCell(c);
						if (cell != null) {
							if (c == 0) {
//							String Id = cell.toString();
								stuId = (int) Math.round(cell.getNumericCellValue());
								if ((stuId) == 0) {
									rowMessage = "学生学号不能为0";
									return rowMessage;
								} else {
									s.setStuId(stuId);
								}
							} else if (c == 1) {
								stuName = cell.getStringCellValue();
								if (StringUtils.isEmpty(stuName)) {
									rowMessage = "数据缺失";
									return rowMessage;
								} else {
									s.setStuName(stuName);
								}
							} else if (c == 2) {
								deptName = cell.getStringCellValue();
								if (StringUtils.isEmpty(deptName)) {
									rowMessage = "数据缺失";
									return rowMessage;
								} else {
									deptId = uploadMapper.selectDeptId(deptName);
									if (deptId == 0) {
										rowMessage = "第" + r + "行，第" + c + "列数据错误";
										return rowMessage;
									} else {
										s.setDeptId(deptId);
									}

								}

							} else if (c == 3) {
								className = cell.getStringCellValue();
								if (StringUtils.isEmpty(className)) {
									rowMessage = "数据缺失";
									return rowMessage;
								} else {
									classId = uploadMapper.selectClassId(className);
									if (classId == 0) {
										rowMessage = "第" + r + "行，第" + c + 1 + "列数据错误";
										return rowMessage;
									}
									s.setClassId(classId);
								}
							}
						}

					}

				log.debug("stuId:" + stuId + "....stuName:" + stuName + "....deptName:" + deptName + "....className:"
						+ className);

				int a = (int) uploadMapper.stuIdTotal(s.getStuId());

				if (a == 1) {
					uploadMapper.Ustudent(s);
					
				} else {
					list.add(s);

				}

			};if(list.size()>0)

	{
		uploadMapper.insertBatch(list);
		rowMessage = "导入成功";
		return rowMessage;
	}

	}catch(
	Exception e)
	{
		e.printStackTrace();
	}finally
	{
		if (is != null) {
			try {
				is.close();
			} catch (IOException e) {
				is = null;
				e.printStackTrace();
			}
		}
	}

	rowMessage="更新成功";return rowMessage;

}}
